﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Configuration;


namespace AccesoADatos
{
   public class AccesoBD
    {
       private static SqlConnection cn;


       //obtener la conexion a la BD
       public static SqlConnection getConexion()
       {
           string cs = ConfigurationManager.ConnectionStrings["TODO_SUSPENSION"].ConnectionString;
           try
           {
               cn = new SqlConnection(cs);
               return cn;
           }
           catch (Exception) { throw; }

       }

       //abro la conexion a la BD
       public SqlConnection AbrirConexion()
       {
           string cs = ConfigurationManager.ConnectionStrings["TODO_SUSPENSION"].ConnectionString;
           try
           {
               cn = new SqlConnection(cs);
               cn.Open();
               return cn;
           }
           catch (Exception) { throw; }

       }

       //cierro la conexion
       public void CerrarConexion()
       {
           if (cn.State == System.Data.ConnectionState.Open)
           { cn.Close(); }
       }

       public DataTable ObtenerTodosProductos(string consulta)
       {
           SqlConnection cn = getConexion();
           SqlCommand cmd = new SqlCommand(consulta, cn);
           SqlDataAdapter da = new SqlDataAdapter(consulta, cn);

           try
           {
               DataTable dt = new DataTable();
               da.Fill(dt);

               cmd.Connection = cn;
               cmd.ExecuteReader();

               CerrarConexion();
               return dt;
           }
           catch (Exception)
           {
               throw;
           }
       }

       //devuelvo un DataReader previo a pasar la consulta SQL
       public SqlDataReader ConsultaConReader(string sql)
       {

           SqlConnection cn = AbrirConexion();
           SqlCommand cmd = new SqlCommand(sql, cn);
           SqlDataReader dr = cmd.ExecuteReader();

           return dr;
       }


       public static SqlDataAdapter selectRpt(string consulta, List<SqlParameter> parametro)
       {
           try
           {
               SqlConnection cn = getConexion();
               SqlCommand com = new SqlCommand(consulta, cn);
               com.CommandType = CommandType.Text;

              foreach (SqlParameter item in parametro)
               {
                   com.Parameters.Add(item);
               }
               SqlDataAdapter da = new SqlDataAdapter(com);
               return da;
           }
           catch (SqlException)
           {
               throw;
           }
           finally
           {
               //cerrarConex();
           }
       }
    }
}
